# -*- coding:utf-8 -*-

from typing import List
from datetime import datetime, time
from module_admin.entity.do.role_do import SysRoleDept
from sqlalchemy import and_, delete, desc, func, or_, select, update
from sqlalchemy.ext.asyncio import AsyncSession
from module_gen.constants.gen_constants import GenConstants
from module_vip.entity.do.bg_vip_do import BgVip
from module_vip.entity.vo.bg_vip_vo import BgVipPageModel, BgVipModel
from utils.page_util import PageUtil, PageResponseModel
from utils.common_util import CamelCaseUtil


class BgVipDao:

    @classmethod
    async def get_by_id(cls, db: AsyncSession, bg_vip_id: int) -> BgVip:
        """根据主键获取单条记录"""
        bg_vip = (((await db.execute(
                            select(BgVip)
                            .where(BgVip.id == bg_vip_id)))
                       .scalars())
                       .first())
        return bg_vip

    @classmethod
    async def get_by_user_id(cls, db: AsyncSession, user_id: int) -> BgVip:
        """根据主键获取单条记录"""
        bg_vip = (((await db.execute(
                            select(BgVip)
                            .where(BgVip.user_id == user_id)))
                       .scalars())
                       .one_or_none())
        return bg_vip

    """
    查询
    """
    @classmethod
    async def get_bg_vip_list(cls, db: AsyncSession,
                             query_object: BgVipPageModel,
                             data_scope_sql: str = None,
                             is_page: bool = False) -> [list | PageResponseModel]:

        query = (
            select(BgVip)
            .where(
                BgVip.begin_date.between(query_object.begin_begin_date, query_object.end_begin_date) if query_object.begin_date else True,
                BgVip.count == query_object.count if query_object.count else True,
                BgVip.end_date.between(query_object.begin_end_date, query_object.end_end_date) if query_object.end_date else True,
                BgVip.status == query_object.status if query_object.status else True,
                BgVip.user_id == query_object.user_id if query_object.user_id else True,
                BgVip.del_flag == '0',
                eval(data_scope_sql) if data_scope_sql else True,
            )
            .order_by(desc(BgVip.create_time))
            .distinct()
        )
        bg_vip_list = await PageUtil.paginate(db, query, query_object.page_num, query_object.page_size, is_page)
        return bg_vip_list


    @classmethod
    async def add_bg_vip(cls, db: AsyncSession, add_model: BgVipModel, auto_commit: bool = True) -> BgVipModel:
        """
        增加
        """
        bg_vip =  BgVip(**add_model.model_dump(exclude_unset=True, ))
        db.add(bg_vip)
        await db.flush()
        bg_vip_model = BgVipModel(**CamelCaseUtil.transform_result(bg_vip))
        if auto_commit:
            await db.commit()
        return bg_vip_model

    @classmethod
    async def edit_bg_vip(cls, db: AsyncSession, edit_model: BgVipModel, auto_commit: bool = True) -> BgVip:
        """
        修改
        """
        edit_dict_data = edit_model.model_dump(exclude_unset=True, exclude={ *GenConstants.DAO_COLUMN_NOT_EDIT })
        await db.execute(update(BgVip), [edit_dict_data])
        await db.flush()
        if auto_commit:
            await db.commit()
        return await cls.get_by_id(db, edit_model.id)

    @classmethod
    async def del_bg_vip(cls, db: AsyncSession, bg_vip_ids: List[str], soft_del: bool = True, auto_commit: bool = True):
        """
        删除
        """
        if soft_del:
            await db.execute(update(BgVip).where(BgVip.id.in_(bg_vip_ids)).values(del_flag='2'))
        else:
            await db.execute(delete(BgVip).where(BgVip.id.in_(bg_vip_ids)))
        await db.flush()
        if auto_commit:
            await db.commit()

    @classmethod
    async def get_vip_statistics(cls, db: AsyncSession) -> dict:
        """
        获取VIP会员统计信息
        """
        # 总VIP会员数
        total_query = select(func.count(BgVip.id)).where(BgVip.del_flag == '0')
        total_result = await db.execute(total_query)
        total_vips = total_result.scalar()

        # 有效VIP会员数
        active_query = select(func.count(BgVip.id)).where(
            BgVip.status == '0',
            BgVip.end_date > datetime.now(),
            BgVip.del_flag == '0'
        )
        active_result = await db.execute(active_query)
        active_vips = active_result.scalar()

        # 即将到期VIP会员数（7天内到期）
        from datetime import timedelta
        expire_date = datetime.now() + timedelta(days=7)
        expiring_query = select(func.count(BgVip.id)).where(
            BgVip.status == '0',
            BgVip.end_date <= expire_date,
            BgVip.end_date > datetime.now(),
            BgVip.del_flag == '0'
        )
        expiring_result = await db.execute(expiring_query)
        expiring_vips = expiring_result.scalar()

        # 已过期VIP会员数
        expired_query = select(func.count(BgVip.id)).where(
            BgVip.end_date <= datetime.now(),
            BgVip.del_flag == '0'
        )
        expired_result = await db.execute(expired_query)
        expired_vips = expired_result.scalar()

        return {
            'totalVips': total_vips,
            'activeVips': active_vips,
            'expiringVips': expiring_vips,
            'expiredVips': expired_vips
        }